package com.itheima.dao.impl;

import com.itheima.dao.UserDao;
import com.itheima.domain.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {
    @Override
    public List<User> findAll() {
        List<User> userList = null;
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://192.168.23.129:3306/day17", "root", "itheima");
            st = conn.createStatement();
            String sql = "select * from user";
            rs = st.executeQuery(sql);

            userList = new ArrayList<>();
            // 处理结果集:  将查询的每一条数据封装到一个User对象, 在将每一个User对象添加到List集合中返回
            while (rs.next()) {
                //1. 创建一个空的User对象
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setGender(rs.getString("gender"));
                user.setAge(rs.getInt("age"));
                user.setAddress(rs.getString("address"));
                user.setQq(rs.getString("qq"));
                user.setEmail(rs.getString("email"));
                //2. 将User对象, 添加到集合中
                userList.add(user);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (st != null) {
                    st.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return userList;
    }


    @Override
    public void save(User user) {
        Connection conn = null;
        Statement st = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://192.168.23.129:3306/day17", "root", "itheima");
            st = conn.createStatement();
            String sql = "insert into user values(null,'"
                    + user.getName() + "','"
                    + user.getGender() + "',"
                    + user.getAge() + ",'"
                    + user.getAddress() + "','"
                    + user.getQq() + "','"
                    + user.getEmail() + "',null,null)";
            st.executeUpdate(sql);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            try {
                if (st != null) {
                    st.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void deleteById(String id) {
        Connection conn = null;
        Statement st = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://192.168.23.129:3306/day17", "root", "itheima");
            st = conn.createStatement();
            String sql = "delete from user where id ="+id;
            st.executeUpdate(sql);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            try {
                if (st != null) {
                    st.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void update(User user) {
        Connection conn = null;
        Statement st = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://192.168.23.129:3306/day17", "root", "itheima");
            st = conn.createStatement();
            String sql = "update user set name = '" + user.getName()
                    + "',gender = '" + user.getGender()
                    + "' ,age = " + user.getAge()
                    + " , address = '" + user.getAddress()
                    + "' , qq = '" + user.getQq()
                    + "', email = '" + user.getEmail()
                    + "' where id = " + user.getId();
            st.executeUpdate(sql);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            try {
                if (st != null) {
                    st.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public User findById(String id) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        User user = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://192.168.23.129:3306/day17", "root", "itheima");
            st = conn.createStatement();
            String sql = "select * from user";
            rs = st.executeQuery(sql);

            // 处理结果集:  将查询的每一条数据封装到一个User对象, 在将每一个User对象添加到List集合中返回
            while (rs.next()) {
                //1. 创建一个空的User对象
                user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setGender(rs.getString("gender"));
                user.setAge(rs.getInt("age"));
                user.setAddress(rs.getString("address"));
                user.setQq(rs.getString("qq"));
                user.setEmail(rs.getString("email"));

            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (st != null) {
                    st.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return user;
    }
}
